package com.thuanpq60023.easyenglish.adapter;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

public class DatabaseAdapter {
	private static final String CREATE_VOCABULARY = "CREATE  TABLE  IF NOT EXISTS eeVocabulary "
			+ "(Word TEXT NOT NULL , Mean TEXT NOT NULL , Type TEXT NOT NULL , Example TEXT, "
			+ "Unit TEXT, CreatedDate TEXT, Right INTEGER NOT NULL  DEFAULT 1, Wrong INTEGER "
			+ "NOT NULL  DEFAULT 1, PRIMARY KEY (Word, Type));";

	private static final String CREATE_UNIT = "CREATE  TABLE IF NOT EXISTS eeUnit "
			+ "(Name TEXT PRIMARY KEY, CreatedDate TEXT NOT NULL);";

	public static final String DATABASE_FILE_PATH = Environment
			.getExternalStorageDirectory().toString() + "/EasyEnglish/Database";

	public static final String DATABASE_NAME = "easyenglish.sqlite";

	private static SQLiteDatabase mDatabase;

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param context
	 *            the Context within which to work
	 */

	public DatabaseAdapter(Context context) {

		File appDbDir = new File(DATABASE_FILE_PATH);

		if (!appDbDir.exists()) {
			appDbDir.mkdirs();
		}

		if (mDatabase == null) {
			mDatabase = getReadableWritableDatabase();
			// Create tables
			onCreate();
		}
	}

	/**
	 * Convenience method for inserting a row into the database.
	 * 
	 * @param table
	 *            The table to insert the row into
	 * @param nullColumnHack
	 *            SQL doesn't allow inserting a completely empty row, so if
	 *            initialValues is empty this column will explicitly be assigned
	 *            a NULL value
	 * @param values
	 *            this map contains the initial column values for the row. The
	 *            keys should be the column names and the values the column
	 *            values
	 * @return the row ID of the newly inserted row, or -1 if an error occurred
	 */

	public long insert(String table, String nullColumnHack, ContentValues values) {
		long result = mDatabase.insert(table, nullColumnHack, values);
		return result;
	}

	/**
	 * Convenience method for updating rows in the database.
	 * 
	 * @param table
	 *            the table to update in
	 * @param values
	 *            a map from column names to new column values. null is a valid
	 *            value that will be translated to NULL.
	 * @param whereClause
	 *            the optional WHERE clause to apply when updating. Passing null
	 *            will update all rows.
	 * @param whereArgs
	 * @return the number of rows affected
	 */
	public int update(String table, ContentValues values, String whereClause,
			String[] whereArgs) {
		int result = mDatabase.update(table, values, whereClause, whereArgs);
		return result;
	}

	/**
	 * Convenience method for deleting rows in the database.
	 * 
	 * @param table
	 *            the table to delete from
	 * @param whereClause
	 *            the optional WHERE clause to apply when deleting. Passing null
	 *            will delete all rows.
	 * @param whereArgs
	 * @return the number of rows affected if a whereClause is passed in, 0
	 *         otherwise. To remove all rows and get a count pass "1" as the
	 *         whereClause.
	 */
	public int delete(String table, String whereClause, String[] whereArgs) {
		int result = mDatabase.delete(table, whereClause, whereArgs);
		return result;
	}

	/**
	 * Query the given table, returning a Cursor over the result set.
	 * 
	 * @param table
	 *            The table name to compile the query against.
	 * @param columns
	 *            A list of which columns to return. Passing null will return
	 *            all columns, which is discouraged to prevent reading data from
	 *            storage that isn't going to be used.
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param selectionArgs
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @return A Cursor object, which is positioned before the first entry
	 */
	public Cursor query(String table, String[] columns, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy) {
		Cursor cur = mDatabase.query(table, columns, selection, selectionArgs,
				groupBy, having, orderBy);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;
	}

	/**
	 * Query the given table, returning a Cursor over the result set.
	 * 
	 * @param table
	 *            The table name to compile the query against.
	 * @param columns
	 *            A list of which columns to return. Passing null will return
	 *            all columns, which is discouraged to prevent reading data from
	 *            storage that isn't going to be used.
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param selectionArgs
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @param limit
	 *            Limits the number of rows returned by the query, formatted as
	 *            LIMIT clause. Passing null denotes no LIMIT clause.
	 * @return A Cursor object, which is positioned before the first entry
	 */
	public Cursor query(String table, String[] columns, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy, String limit) {
		Cursor cur = mDatabase.query(table, columns, selection, selectionArgs,
				groupBy, having, orderBy, limit);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;
	}

	/**
	 * Query the given URL, returning a Cursor over the result set.
	 * 
	 * @param distinct
	 *            true if you want each row to be unique, false otherwise.
	 * @param table
	 *            The table name to compile the query against.
	 * @param columns
	 *            A list of which columns to return. Passing null will return
	 *            all columns, which is discouraged to prevent reading data from
	 *            storage that isn't going to be used.
	 * @param selection
	 *            A filter declaring which rows to return, formatted as an SQL
	 *            WHERE clause (excluding the WHERE itself). Passing null will
	 *            return all rows for the given table.
	 * @param selectionArgs
	 *            You may include ?s in selection, which will be replaced by the
	 *            values from selectionArgs, in order that they appear in the
	 *            selection. The values will be bound as Strings.
	 * @param groupBy
	 *            A filter declaring how to group rows, formatted as an SQL
	 *            GROUP BY clause (excluding the GROUP BY itself). Passing null
	 *            will cause the rows to not be grouped.
	 * @param having
	 *            A filter declare which row groups to include in the cursor, if
	 *            row grouping is being used, formatted as an SQL HAVING clause
	 *            (excluding the HAVING itself). Passing null will cause all row
	 *            groups to be included, and is required when row grouping is
	 *            not being used.
	 * @param orderBy
	 *            How to order the rows, formatted as an SQL ORDER BY clause
	 *            (excluding the ORDER BY itself). Passing null will use the
	 *            default sort order, which may be unordered.
	 * @param limit
	 *            Limits the number of rows returned by the query, formatted as
	 *            LIMIT clause. Passing null denotes no LIMIT clause.
	 * @return A Cursor object, which is positioned before the first entry
	 */
	public Cursor query(boolean distinct, String table, String[] columns,
			String selection, String[] selectionArgs, String groupBy,
			String having, String orderBy, String limit) {
		Cursor cur = mDatabase.query(distinct, table, columns, selection,
				selectionArgs, groupBy, having, orderBy, limit);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;
	}

	/**
	 * Runs the provided SQL and returns a Cursor over the result set.
	 * 
	 * @param sql
	 *            the SQL query. The SQL string must not be ; terminated
	 * @param selectionArgs
	 *            You may include ?s in where clause in the query, which will be
	 *            replaced by the values from selectionArgs. The values will be
	 *            bound as Strings.
	 * @return A Cursor object, which is positioned before the first entry
	 */
	public Cursor rawQuery(String sql, String[] selectionArgs) {
		Cursor cur = mDatabase.rawQuery(sql, selectionArgs);
		if (cur != null) {
			cur.moveToFirst();
		}
		return cur;
	}

	/**
	 * Execute a single SQL statement that is not a query. For example, CREATE
	 * TABLE, DELETE, INSERT, etc. Multiple statements separated by ;s are not
	 * supported. it takes a write lock
	 * 
	 * @param sql
	 * @throws SQLException
	 *             - If the SQL string is invalid for some reason
	 */
	public void execSQL(String sql) throws SQLException {
		mDatabase.execSQL(sql);
	}

	/**
	 * Execute a single SQL statement that is not a query. For example, CREATE
	 * TABLE, DELETE, INSERT, etc. Multiple statements separated by ;s are not
	 * supported. it takes a write lock
	 * 
	 * @param sql
	 * @param bindArgs
	 *            only byte[], String, Long and Double are supported in
	 *            bindArgs.
	 * @throws SQLException
	 *             - If the SQL string is invalid for some reason
	 */
	public void execSQL(String sql, Object[] bindArgs) throws SQLException {
		mDatabase.execSQL(sql, bindArgs);
	}

	/**
	 * Crate table
	 */

	public void onCreate() {
		mDatabase.execSQL(CREATE_UNIT);
		mDatabase.execSQL(CREATE_VOCABULARY);
	}

	public void onUpgrade() {
		mDatabase.execSQL("DROP TABLE IF EXISTS eeVocabulary");
		mDatabase.execSQL("DROP TABLE IF EXISTS eeUnit");
		onCreate();
	}

	/**
	 * Close database
	 */
	public void close() {
		if (mDatabase != null) {
			mDatabase.close();
			mDatabase = null;
		}
	}

	/**
	 * Get readable - writable database from SDcard
	 * 
	 * @return SQLiteDatabase
	 */

	public SQLiteDatabase getReadableWritableDatabase() {
		SQLiteDatabase db = SQLiteDatabase.openDatabase(DATABASE_FILE_PATH
				+ File.separator + DATABASE_NAME, null,
				SQLiteDatabase.OPEN_READWRITE
						+ SQLiteDatabase.CREATE_IF_NECESSARY);
		return db;
	}
}
